package com.dao;

import com.model.*;
import com.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wangjg
 * @date 2023/11/1 15:15
 * @Description: 用户请求处理DAO
 */
public class UserDao {

    /**
     * 登录信息
     * @param con   数据库连接
     * @param user  用户登录信息
     * @return      {@link User}
     * @throws Exception    连接异常
     */
    public User login(Connection con, User user)throws Exception {
        User resultUser = null;
        String sql = "select * from t_user where userName=? and password=? and userType=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, user.getUserName());
        preparedStatement.setString(2, user.getPassword());
        preparedStatement.setInt(3, user.getUserType());
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()) {
            resultUser = new User();
            resultUser.setId(rs.getInt("id"));
            resultUser.setUserName(rs.getString("userName"));
            resultUser.setPassword(rs.getString("password"));
            resultUser.setName(rs.getString("name"));
            resultUser.setNum(rs.getString("num"));
            resultUser.setSex(rs.getString("sex"));
            resultUser.setTel(rs.getString("tel"));
            resultUser.setRoomNum(rs.getString("roomNum"));
            resultUser.setRoomId(rs.getInt("roomId"));
            resultUser.setBuildId(rs.getInt("buildId"));
            resultUser.setBuildName(rs.getString("buildName"));
            resultUser.setUserType(rs.getInt("userType"));
        }
        return resultUser;
    }

    /**
     * 根据ID获取用户信息
     * @param con   数据库连接
     * @param id    用户id
     * @return      {@link User}
     * @throws Exception    连接异常
     */
    public User userInfo(Connection con, String id)throws Exception {
        String sql = "select * from t_user t1 where t1.id=?";
        PreparedStatement preparedStatement=con.prepareStatement(sql);
        preparedStatement.setString(1, id);
        ResultSet rs=preparedStatement.executeQuery();
        User resultUser = new User();
        if(rs.next()) {
            resultUser = new User();
            resultUser.setId(rs.getInt("id"));
            resultUser.setUserName(rs.getString("userName"));
            resultUser.setPassword(rs.getString("password"));
            resultUser.setName(rs.getString("name"));
            resultUser.setNum(rs.getString("num"));
            resultUser.setSex(rs.getString("sex"));
            resultUser.setTel(rs.getString("tel"));
            resultUser.setRoomNum(rs.getString("roomNum"));
            resultUser.setRoomId(rs.getInt("roomId"));
            resultUser.setBuildId(rs.getInt("buildId"));
            resultUser.setBuildName(rs.getString("buildName"));
            resultUser.setUserType(rs.getInt("userType"));
        }
        return resultUser;
    }

    /**
     * 根据获取用户分页信息
     * @param con   数据库连接
     * @param pageBean    分页对象
     * @param user    用户信息
     * @return      {@link User}
     * @throws Exception    连接异常
     */
    public List<User> userList(Connection con, PageBean pageBean, User user)throws Exception {
        List<User> userList = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT * FROM t_user t1 where 1=1");
        if(StringUtil.isNotEmpty(user.getName())) {
            sb.append(" and t1.name like '%").append(user.getName()).append("%'");
        } else if(StringUtil.isNotEmpty(user.getUserName())) {
            sb.append(" and t1.userName like '%").append(user.getUserName()).append("%'");
        }else if(StringUtil.isNotEmpty(user.getRoomNum())) {
            sb.append(" and t1.roomNum like '%").append(user.getRoomNum()).append("%'");
        }else if(StringUtil.isNotEmpty(user.getBuildName())) {
            sb.append(" and t1.buildName like '%").append(user.getBuildName()).append("%'");
        }else if(null != user.getBuildId()){
            sb.append(" and t1.buildId = ").append(user.getBuildId());
        }else if(StringUtil.isNotEmpty(user.getNum())){
            sb.append(" and t1.num like '%").append(user.getNum()).append("%'");
        }
        if(pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getPageSize());
        }
        PreparedStatement preparedStatement = con.prepareStatement(sb.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while(rs.next()) {
            User resultUser = new User();
            resultUser.setId(rs.getInt("id"));
            resultUser.setUserName(rs.getString("userName"));
            resultUser.setPassword(rs.getString("password"));
            resultUser.setNum(rs.getString("num"));
            resultUser.setName(rs.getString("name"));
            resultUser.setSex(rs.getString("sex"));
            resultUser.setTel(rs.getString("tel"));
            resultUser.setRoomNum(rs.getString("roomNum"));
            resultUser.setRoomId(rs.getInt("roomId"));
            resultUser.setBuildId(rs.getInt("buildId"));
            resultUser.setBuildName(rs.getString("buildName"));
            resultUser.setUserType(rs.getInt("userType"));
            userList.add(resultUser);
        }
        return userList;
    }

    /**
     * 修改密码
     * @param con   数据库连接
     * @param id    用户ID
     * @param password    密码
     * @return      {@link User}
     * @throws Exception    连接异常
     */
    public int updatePassword(Connection con, int id, String password)throws Exception {
        String sql = "update t_user set password=? where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, password);
        preparedStatement.setInt(2, id);
        return preparedStatement.executeUpdate();
    }

    /**
     * 新增用户
     * @param con   数据库连接
     * @param user    用户信息
     * @return      {@link Integer}
     * @throws Exception    连接异常
     */
    public int addUser(Connection con, User user)throws Exception {
        String sql = "insert into t_user values(null,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, user.getUserName());
        preparedStatement.setString(2, user.getPassword());
        preparedStatement.setString(3, user.getName());
        preparedStatement.setString(4, user.getNum());
        preparedStatement.setInt(5, user.getBuildId());
        preparedStatement.setString(6, user.getBuildName());
        preparedStatement.setInt(7, user.getRoomId());
        preparedStatement.setString(8, user.getRoomNum());
        preparedStatement.setString(9, user.getSex());
        preparedStatement.setString(10, user.getTel());
        preparedStatement.setInt(11, user.getUserType());
        return preparedStatement.executeUpdate();
    }

    /**
     * 更新用户
     * @param con   数据库连接
     * @param user    用户信息
     * @return      {@link Integer}
     * @throws Exception    连接异常
     */
    public int updateUser(Connection con, User user)throws Exception {
        String sql = "update t_user set userName=?,name=?,num=?,buildId=?,buildName=?,roomId=?,roomNum=?,sex=?,tel=? where id =?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, user.getUserName());
        preparedStatement.setString(2, user.getName());
        preparedStatement.setString(3, user.getNum());
        preparedStatement.setInt(4, user.getBuildId());
        preparedStatement.setString(5, user.getBuildName());
        preparedStatement.setInt(6, user.getRoomId());
        preparedStatement.setString(7, user.getRoomNum());
        preparedStatement.setString(8, user.getSex());
        preparedStatement.setString(9, user.getTel());
        preparedStatement.setInt(10, user.getId());
        return preparedStatement.executeUpdate();
    }

    /**
     * 删除
     * @param con   数据库连接
     * @param userId    用户ID
     * @return      {@link Integer}
     * @throws Exception    连接异常
     */
    public int deleteUser(Connection con, String userId)throws Exception {
        String sql = "delete from t_user where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, userId);
        return preparedStatement.executeUpdate();
    }

    /**
     * 查询用户总数
     * @param con   数据库连接
     * @param user    用户信息
     * @return      {@link User}
     * @throws Exception    连接异常
     */
    public int userCount(Connection con, User user)throws Exception {
        StringBuilder sb = new StringBuilder("select count(*) as total from t_user t1");
        if(StringUtil.isNotEmpty(user.getName())) {
            sb.append(" where t1.name like '%").append(user.getName()).append("%'");
        } else if(StringUtil.isNotEmpty(user.getUserName())) {
            sb.append(" where t1.userName like '%").append(user.getUserName()).append("%'");
        }
        PreparedStatement preparedStatement = con.prepareStatement(sb.toString());
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()) {
            return rs.getInt("total");
        } else {
            return 0;
        }
    }
}
